• 27 — The Data Control
• by Brad Shannon
• What Is the Data Control?
• Bound Controls and the Data Control
• Additional Properties Provided with the Data Control
• Align
• BOFAction
• Connect
• DataBaseName
• EOFAction
• Exclusive
• Options
• ReadOnly
• RecordsetType
• RecordSource
• Recordset
• Additional Events Provided with the Data Control
• Error
• Reposition
• Validate
• Additional Methods Provided with the Data Control
• Refresh
• UpdateControls
• UpdateRecord
• Using the Data Control with Non-Access Databases
• Using the Data Control to Delete Records
• Summary

27 — The Data Control

by Brad Shannon

This chapter will cover the various properties, methods, and events of the Data control. There will be examples provided throughout in order to illustrate the capabilities of the control.

What Is the Data Control?

The Data control is a component of Visual Basic that allows your application to interact with databases of different formats. Using the Data control, you can open a database and define the contents of the underlying Recordset.

A Recordset is a collection of records that are extracted from the database and then manipulated by the Data control. There are three types of Recordsets: dynaset, snapshot, and table. Each of these Recordsets has a variety of properties and methods that you can apply to it. This chapter and Chapter 28, "The Access Jet Engine," discuss the properties and methods of the Data control and the underlying Recordset.

Note

The constants used in the chapter are now included in the Visual Basic Environment. There is no need to include an external Constants.txt file.

Through the Data control's RecordSource and RecordSourceType properties, you define the Recordset that your application will work with. Using the Data control, you can view, edit, and update records in the underlying Recordset. You cannot use the Data control to delete records or create a new database.

The Data control supports the following database formats:

• MS Access
  
  
• Btrieve
  
  
• dBASE
  
  
• Excel
  
  
• FoxPro
  
  
• Paradox
  
  
• Text files
  
  
• ODBC
  
  

MS Access is the most flexible format of database to use with Visual Basic because MS Access and Visual Basic share the same database engine (called Jet).

Bound Controls and the Data Control

Using a combination of bound controls and the Data control, you can create an application that will allow you to move through the records in a Recordset. You can edit these records or add new records to the Recordset with a minimum of programming effort.

Bound controls provide direct access to the contents of a field in a database. The fields that are available to the bound control are defined by The Recordset contained in the Data control. The DataSource and DataField properties are used in order to define the contents of a bound control.

Bound controls support all of the Standard Visual Basic text editing and formatting properties and methods. Additionally, bound controls support the DataSource, DataField, and DataChanged properties. The DataSource property is a read/write property at both run time and design time that refers to the Data control that is resident on the same form as the bound control. The DataField property is a read/write property at both run time and design time that refers to a field contained in the Recordset held by the Data control. The DataChanged property is a read-only, run-time property that is used in order to determine whether a change has been made to the underlying Recordset through a bound control. Any changes you make to the contents of the bound controls will be automatically updated to the Recordset whenever the record pointer of the Data control is altered either through the use of the Data control's record movement buttons or through actions performed in your application.

The Data control provides navigation buttons (shown in Figure 27.1) for you to use to move through records.

Figure 27.1. The Data control navigation buttons.

The following example shows how to use the Data control and bound text boxes to display and edit values stored in the TITLES table of the BIBLIO.MDB database that is supplied with Visual Basic. Follow these steps:

1. Open a new project.
   
   
2. Place the following controls on the form:
   

Control

Name

Caption

Top

Left

Width

Height

1. Set the following properties for the Data1 Data control:
   
   DataBaseName: BIBLIO.MDB
   RecordSource: Titles
   
   

---

Note

The BIBLIO.MDB database referred to throughout this chapter can be found in the directory containing Visual Basic.


---

2. Set the DataSource and DataField properties for the text boxes that you have added to the form.
   
   You set the DataSource property by entering the name of the Data control. In this case, type data1 or select it from the combo box that appears in the DataSource property list box. After you set the DataSource property, you set the DataField property by entering the name of the field into the underlying Recordset or by selecting the name of the field from the combo box that appears in the DataField property. The following is the code that results from this step:
   
   TextBox Txtpubid
   
   DataSource = data1
   DataField = pubid
   
   TextBox Txtyear
   
   DataSource = data1
   DataField = Year Published
   
   TextBox Txtisbn
   
   DataSource = data1
   DataField = ISBN
   
   TextBox Txttitle
   
   DataSource = data1
   DataField = Title
   
   TextBox Txtsubject
   
   DataSource = data1
   DataField = Subject
   
   TextBox Txtdescription
   
   DataSource = data1
   DataField = Description
   
   TextBox Txtcomments
   
   DataSource = data1
   DataField = comments
   
   TextBox Txtnotes
   
   DataSource = data1
   DataField = notes
   
   
3. In the Click event for the cmdExit control, enter the following line:
   
   Unload Me
   
   
4. Run the project. A window much like the one shown in Figure 27.2 should appear.
   
   

Figure 27.2. The bound fields illustrates the Data control.

You can use the Data control's record navigation buttons to move through all of the records contained in the titles Recordset. Additionally, you can make changes to the contents of the Recordset by altering the information contained in the text boxes and using the Data control to change the current record.

The preceding example is a very simple examination of the capabilities of the Data control. The rest of this chapter builds on this example in order to fully explore the different properties, methods, and events available to the Data control.

Additional Properties Provided with the Data Control

The Data control supports all of the standard properties, such as control placement, that are associated with Visual Basic controls. It also supports properties that relate to data manipulation. You can manipulate these data manipulation properties for read/write access at both design and run time. The following sections describe each of the data manipulation properties.

Align

You use the Align property to determine the location of the Data control in relation to the form at run time as well as design time. You can align the Data control with the top, left, bottom, or right edge of the form. You also can choose to have no alignment and place the control anywhere on the form. The default value of the Align property is no alignment.

Add the following line to the Form_Load event or make the change using the properties editor and run your project:

Data1.align= 2

The Data control should now be aligned along the button of the form under the Exit button.

BOFAction

The BOFAction property determines what action the Data control takes when the BOF (beginning of file) property of the Recordset is true. MoveFirst and BOF are the two possible settings for the BOFAction property.

The MoveFirst setting causes the Data control to reposition the Recordset to the first record in the set and make it the current record when the BOF condition is true. To set the BOFAction property to this setting at run time, use the following expression (data1 represents the name of the Data control):

data1.BOFaction= vbBOFActionMoveFirst

If the BOFAction property is set to BOF, the Data control takes no action when the BOF condition is true. At this point, there is no current record and any attempt to edit the displayed record results in a No Current Record error message. If the Data control is visible, then the move to previous navigation button is disabled. To set the BOFAction property to this setting at run time, use the following expression (data1 represents the name of the Data control):

data1.BOFaction= vbBOFActionBOF

Note

The default of the BOFAction property is to move to the first record. In most circumstances, this action is the desired result as it ensures that you have a valid current record.

To demonstrate the behavior of the BOFAction property, perform the following steps.

1. Add the following line to the Form_Load event or use the properties editor to make the change:
   
   data1.BOFAction= vbBOFActionBOF
   
   
2. Add a check box control with the following properties to the form:
   
   Name = chkbof
   Caption = BOF Reached
   Enabled= false
   Top = 3660
   Left= 180
   Height = 295
   Width = 1875
   
   
3. Add the following to the Data1.reposition event:
   
   If Data1.Recordset.BOF = True Then
   
   chkbof.Value = vbchecked
   
   Else
   
   chkbof.Value = vbunchecked
   
   End If
   
   
4. Run your project.
   
   
5. Select the move to first record navigation button on the Data control. The TextBoxes on the form will now display the contents of the fields of the first record in the Recordset.
   
   
6. Select the move to previous record navigation button on the Data control. The previous record button should now be dimmed, indicating that you are at the BOF. At this point, the Data control no longer has a valid record pointer. The BOF check box will also be selected, providing another indication that you are in the BOF state (see Figure 27.3).
   
   If at this point you were to make a change to the displayed record and subsequently try to move to a different record using one of the navigation buttons on the Data control, you would receive a run-time error because you tried to update an invalid record.
   
   Once you have ended the application, Reset the BOFAction property back to the MoveFirst setting.
   
   

Figure 27.3. This is how a screen looks when you are at the beginning of a file.

Connect

You use the Connect property when you are accessing databases from a program other than MS Access. The Connect property specifies the type of database that you are connecting to. Note that only files of the specified type are displayed in the Database Selection dialog box. If you are connecting to an ODBC database, for example, you need to specify ODBC in the Connect property and perhaps supply additional information such as USERID and PASSWORD. The additional information depends on the type of ODBC database you're using. (The ODBC database driver supplies further information on the connect string for ODBC databases.)

The following is an example of an expression that sets the Connect property at run time:

data1.connect= " dBASE III;"

DataBaseName

The contents of the DataBaseName property depend on what type of database you're connecting to. If you are connecting to an MS Access database, the property contains the name of the database that contains the tables you want to access. If you are connecting to a Btrieve database, the property contains the name of the Btrieve Data Definition file relating to the tables you want to access. If you are connecting to an ODBC database, the property is blank because the name of the data source is determined by settings in the Connect property . For all other supported databases, the DataBaseName property is set to the directory that contains the files that contain the data you want to access. To select the database file or the directory from a file selection dialog box, select the (...) in the properties editor.

Note

If you change the DataBaseName property at run time, you must execute the Refresh method in order for the change to take effect.

Follow these steps to learn how the DataBaseName property works:

1. Remove the reference to the DataBaseName property of the Data control.
   
   
2. Add the following lines to the Form_Load event in your project:
   
   dim filepath as string
   filepath="c:\vb40\"
   data1.databasename= filepath&"biblio.mdb"
   data1.refresh
   
   

---

Note

The value assigned to the file path variable should be the directory in which Visual Basic is installed.


---

3. Run your project. The project should behave in the same manner as when you examined the behavior of the BOFAction property.
   
   

Note

In a real-world application, the path to the database would probably be stored as a public variable that is initialized as part of your application start up. It is good practice to keep the file path of your data files external to your application, either in an application INI file or in a separate database. Doing this provides additional flexibility in the setting up and distribution of your application. The initial setting of the path could be a task completed by your installation program.

EOFAction

The EOFAction property determines what action the Data control takes when the EOF (end of file) property is true. MoveLast, EOF, and AddNew are the possible settings for this property.

The MoveLast setting causes the Data control to reposition the Recordset to the last record and make that record the current record when the Data control encounters an EOF condition of true. The following expression demonstrates how to specify this setting at run time (data1 is the name of the Data control):

data1.EOFaction= vbEOFActionMoveLast

The EOF setting causes the Data control to take no action when the EOF condition is true. At this point, there is no current record and any attempt to edit the displayed record results in a No Current Record error message. If the Data control is visible, the move to the next record button is disabled. To specify this setting at run time, use the following expression:

data1.EOFAction= vbEOFActionEOF

The AddNew setting causes the Data control to add a new record to the table and make that record the current record when the EOF condition is true. To specify this setting at run time, use the following expression:

data1.EOFaction= vbEOFActionAddNew

Note

The default action of the EOFAction property is to move to the last record in the Recordset when an EOF condition of true occurs. In most circumstances, this setting is the one you want because it ensures that you have a valid current record and gives you better control of the circumstances in which records can be added.

Follow these steps to use the AddNew setting of the EOFAction property:

1. Add the following line to the Form_Load event or use the properties editor to make the appropriate change to the EOFAction property:
   
   dat1.EOFaction= vbEOFActionAddNew
   
   
2. Add a check box control to the form with the following properties:
   
   Name = chkEof
   Caption = EOF Reached
   Enabled= false
   Top = 3660
   Left= 2340
   Height = 295
   Width = 1875
   
   
3. In the Data1.Reposition event, add the following:
   
   If Data1.Recordset.EOF = True Then
   
   chkeof.Value = vbchecked
   
   Else
   
   chkeof.Value = vbunchecked
   
   End If
   
   
4. Run your project.
   
   
5. Select the move to last record button on the Data control. The last record in the Recordset is displayed.
   
   
6. Select the move to the next record button on the Data control, the Data control automatically adds a new blank record to the Recordset. Enter relevant data into the text boxes.
   
   
7. Select the move to the next record button. The contents of the newly created record are added to the Recordset, and you are positioned on another new record.
   
   

Exclusive

The Exclusive property determines whether your application is opening the database for exclusive use, which means no other users can access the database. You can set this property to true (no other users can access the database) or false (other users are able to access the database). The following expression shows how you can set this property at run time:

dim filepath as string

filepath="c:\vb40\"

data1.databasename= filepath&"biblio.mdb"

data1.exclusive= true

data1.refresh

Note

If you change the Exclusive property at run time, you must execute the Refresh method in order for the change to take effect.

To practice using the Exclusive property, perform the following steps:

1. Change the Exclusive property to true by using the properties editor.
   
   
2. Run your project.
   
   
3. While your project is running, open the BIBLIO database using the data manager application that is distributed with Visual Basic or MS Access. When you try to access the file, you should receive a message indicating that the file cannot be opened.
   
   
4. Exit your project and reset the Exclusive property back to false.
   
   

Options

The Options property specifies the characteristics of the Recordset that is created by the Data control. This property is predominantly used in a multi-user environment. The following constants are available in order to set the options at run time:

• dbDenywrite. This constant ensures that the other users in a multi-user environment cannot write any changes to the records contained in the Recordset.
  
  
• dbDenyRead. This constant ensures that other users are unable to read records contained in the Recordset. This setting applies only to the table type Recordset.
  
  
• dbReadOnly. With this constant in place, the Recordset is opened as read only and no changes can be made.
  
  
• dbAppendOnly. This constant lets you add records to the Recordset, but it prevents you from updating or deleting existing records.
  
  
• dbInconsistent. On a joined Recordset, this constant lets you update only those fields that do not affect the join condition.
  
  
• dbSQLPassThrough. If you are using an ODBC database, such as SQL Server or Oracle, this constant specifies that SQL expression that is used to create the Recordset be executed on the Server as opposed to using the Access database engine.
  
  
• dbForwardOnly. This constant sets the Recordset so that the only movement possible is forward (only the MoveNext method will have any effect). You cannot set this option on a Data control.
  
  
• dbSeeChange. This constant causes a trappable error if another user is making a change to the record that is currently being edited by someone else.
  
  

You can select multiple options by adding these constants together. By using the following expression to set the Options property at run time, for example, you can ensure that you have exclusive usage of the records in your Recordset:

dim filepath as string

filepath="c:\vb40\"

data1.databasename= filepath&"biblio.mdb"

data1.option= dbDenywrite + dbreadonly

data1.refresh

Note

If you change the Options property at run time, you must execute the Refresh method in order for the change to take effect.

ReadOnly

The ReadOnly property indicates whether the Recordset opened with the Data control can be edited. The following is an example of an expression you could use to set this property at run time:

dim filepath as string

filepath="c:\vb40\"

data1.databasename= filepath&"biblio.mdb"

data1.readonly= True

data1.refresh

Prevents any changes being made to the Recordset

To practice using the ReadOnly property, perform the following steps.

1. Add the following line to the Form_Load event or change the property by using the properties editor.
   
   data1.readonly= True
   
   
2. Run your project.
   
   
3. Use the move to the last record button on the Data control to move to the end of the Recordset.
   
   
4. Select the move to the next record button, which should, based on our previous setting, add a new record. An error message saying that the Recordset is read only appears.
   
   
5. Exit your project and set the ReadOnly property back to false and reset the EOFAction property back to the MoveLast setting.
   
   

Note

If you change the ReadOnly property at run time, you must execute the Refresh method in order for the change to take effect.

RecordsetType

The RecordsetType property determines that type of Recordset that the Data control creates. The following constants are available to define the Recordset at run time:

• vbRStypetable. This constant opens a table-type Recordset.
  
  
• vbRSTypeDynaset. This constant opens a dynaset-type Recordset.
  
  
• vbRSTypeSnapshot. This constant opens a snapshot-type Recordset.
  
  

In the following example, the code defines a snapshot type of Recordset:

dim filepath as string

filepath="c:\vb40\"

data1.databasename= filepath&"biblio.mdb"

data1.recordsettype= vbRSTypeSnapShot

data1.refresh

Each of the Recordset types contains different methods and properties. Subsequent chapters discuss the methods and properties associated with each Recordset type.

RecordSource

The RecordSource property determines the source of the records that make up the Recordset that is held by the Data control. This RecordSource property can contain the name of a table stored in the database, an SQL statement selecting the table and the fields that are to be part of the Recordset, or the name of a QueryDef that is stored in your database.

Note

Setting the RecordSource property to a QueryDef is only supported with MS Access databases.

Note

If you change the RecordSource property at run time, you must execute the Refresh method in order for the change to take effect.

Perform the following steps to practice changing the RecordSource:

1. Add a new form to your project named form4.
   
   
2. In the Project Options dialog box, change the start-up form to Form.
   
   
3. Add a Data control to the form; all relevant properties will be set at run time.
   
   
4. Add a Data Bound Grid with the following properties to the form:
   
   DataSource : Data1
   Top = 60
   Left= 60
   Height = 2895
   Width = 6675
   
   
5. Add a combo box control with the following properties to the form:
   
   Name = comField
   Top = 3120
   Left= 1560
   Height = 315
   Width = 1875
   
   
6. Add a Label control with the following properties to the form:
   
   Name= lblField
   Caption= Order By
   Top= 3180
   Width = 1215
   Height= 285
   left = 180
   
   
7. Add the following code to the Form_Load event:
   
   Sub Form_Load
   
   Dim filepath As String
   dim x as integer
   filepath = "c:\vb40\"
   Data1.DatabaseName = filepath & "biblio.mdb"
   Data1.RecordSource = "select * From titles order by pubid ,[year published]"
   Data1.Refresh
   
   
   For x = 0 To Data1.Recordset.Fields.Count - 1
   
   comfield.AddItem Data1.Recordset.Fields(x).Name
   
   Next x
   
   end sub
   
   
8. Add the following code to the comfield_Click event:
   
   Private Sub comfield_Click()
   
   If comfield.Text <> "Comments" Then
   Data1.RecordSource = "select * From titles order by [" & _comfield.Text & "]"
   Data1.Refresh
   End If
   
   End Sub
   
   
9. Run your project.
   
   

The Recordset as displayed in the Grid is ordered based on the field that you select for the combo box control (see Figure 27.4). The only exception to this rule would be if you selected the Comments field. In this case, the Recordset order would not change because it is impossible to order a Recordset by a memo type field.

Figure 27.4. Illustrates the Recordset after it has been sorted by title.

Note

If you are unsure of the field names that will be used to order a Recordset, place "[ ]" around the field names. Otherwise, a runtime error will occur if a field name contains embedded spaces.

Recordset

As previously discussed, a Recordset is a collection of records that is held by the Data control. The RecordSource property of the Data control determines the source of the data contained in the Recordset. The RecordsetType property of the Data control determines the type of the Recordset that the Data control will contain. Through the Recordset property, you are able to manipulate the properties and methods of the Recordset associated with the Data control in the same way that you manipulate a Recordset object. Chapter 28, "The Access Jet Engine," discusses the various properties and methods that apply to the Recordset object.

Perform the following steps to practice using the Recordset property:

1. Make a copy of the Form1 form and name it Form2.
   
   
2. In the Project Options dialog box, change the start-up form to Form2.
   
   
3. Alter the Form2 caption to Unbound Form.
   
   
4. Remove the information that bounds the text boxes to the Data control by resetting the DataField and DataSource properties to blanks.
   
   
5. Add the following code to the Data1_Reposition event:
   
   Private Sub Data1_Reposition()
   
   
   If Data1.Recordset.BOF = True Then
   
   chkbof.Value = vbChecked
   Exit Sub
   
   End If
   
   If Data1.Recordset.EOF = True Then
   
   chkeof.Value = vbChecked
   Exit Sub
   
   End If
   
   chkbof.Value = vbUnchecked
   chkeof.Value = vbUnchecked
   
   txtcomments.Text = getfield(Data1.Recordset, "comments")
   txtdescription.Text = getfield(Data1.Recordset, "description")
   txtnotes.Text = getfield(Data1.Recordset, "notes")
   txtpubid.Text = getfield(Data1.Recordset, "pubid")
   txtsubject.Text = getfield(Data1.Recordset, "subject")
   txttitle.Text = getfield(Data1.Recordset, "title")
   txtyear.Text = getfield(Data1.Recordset, "[year published]")
   txtisbn.Text = getfield(Data1.Recordset, "isbn")
   
   End Sub
   
   
6. In the General section of Form2, add the following function:
   
   Function getfield(rs As Recordset, cfield As String) as variant
   If IsNull(rs.Fields(cfield).Value) Then
   
   Select Case rs.Fields(cfield).Type
   
   Case dbBoolean
   
   getfield = False
   
   Case dbByte To dbDate
   
   getfield = 0
   
   Case dbLongBinary
   
   getfield = 0&
   
   Case dbMemo, dbText
   
   getfield = ""
   
   End Select
   
   
   Else
   
   getfield = rs.Fields(cfield).Value
   
   End If
   
   End Function
   
   
7. Run your project.
   
   When you select the Data control's navigation buttons, the text boxes on the display will be filled using the data from the Titles Recordset. The update of the controls occurs during the Data1_Reposition event. The purpose of the Getfield function is to retrieve the value of the field in the Recordset. If the value of the field is #NULL#, the default null value based on the type of the field is returned. If the field is numeric, for example, it returns 0. If the field is a string type field, it returns blank. Otherwise, the contents of the field are returned.
   
   

---

Note

Trying to return a #NULL# value to a text box results in a run-time error.


---

8. After you complete this example, change the start-up form in your project back to FORM1, as you will be using this form for the remaining examples.
   
   

Additional Events Provided with the Data Control

The Data control supports all of the standard events, such as Drag, and mouse movement events that are associated with Visual Basic controls. In addition to the standard events, the Data control supports events that relate to data manipulation. This section describes the events that relate to data manipulation.

Error

The Error event occurs when there is a data access error that is not related to any of your code and therefore cannot be trapped by standard Visual Basic error handling. An example of this type of error would be if the design time properties that have been set up in the Data control become invalid at run time, for example, if a field name has been changed but the DataField property in the related text box has not been altered. The Error event consists of two parts. DataErr is the error code representation of the error that occurred. Response is the response that you want to make in the error condition.

The following constants are available to set the response:

• vbDataErrContinue. This constant continues executing the program.
  
  
• vbDataErrDisplay. This constant displays an error dialog box.
  
  

To practice handling an error event, make the following changes to your project:

1. Change the start-up form of your project to Form1.
   
   
2. Change the DataField property of the TxtIsbn control to "test."
   
   
3. Add the following code to the Data1_Error event:
   
   Private Sub Data1_Error(DataErr As Integer, Response As Integer)
   
   If DataErr > 0 Then
   
   MsgBox " The Following Error Occurred " & DataErr & " " & Error$
   
   End If
   
   
   End Sub
   
   
4. Run your project.
   
   

You will now receive a message box prior to your Form_Loading that indicates that an item was not found in the fields collection.

Reposition

The Reposition event occurs when a new record becomes the current record. The event can occur as a result of the selection of one of the record navigation buttons on the Data control or when the record is changed in your code through the use of one of the move or find methods of the Data control or Recordset. The Reposition event has no parameters. The Reposition event differs from the Validate event in that the Validate event occurs before the current record has been changed and the Reposition event occurs after the new record has been made current. You would use the Reposition event in order to perform an operation each time that the current record in the Recordset has been changed. The Unbound Form example under the earlier Recordset heading in the chapter illustrated this kind of situation.

Validate

The Validate event occurs prior to the current record in the Recordset changing. The following parameters are available in the Validate event:

• Save. This parameter indicates if any of the bound controls' data has changed. The value of this parameter is determined via the DataChanged property in the bound controls on the form. A value of true indicates that there has been a change in at least one of the bound controls on the form. A value of false indicates that no changes have been made to the bound controls.
  
  
• Action. This parameter represents the action that has caused the Validate event.
  
  

The following constants are available in order to determine what action triggered the Validate event:

• vbDataActionMoveFirst. The user has selected the move to the first record button on the Data control, or the MoveFirst method has been executed against the Recordset held by the Data control.
  
  
• vbDataActionMovePrevious. The user has selected the move to the previous record button on the Data control, or the MovePrevious method has been executed against a Recordset held by the Data control.
  
  
• vbDataActionMoveNext. The user has selected the move to the next record button on the Data control, or the MoveNext method has been executed against the Recordset held by the Data control.
  
  
• vbDataActionMoveLast. The user has selected the move to the last record button on the Data control, or the MoveLast method has been used against the Recordset held by the Data control.
  
  
• vbDataActionAddNew. The AddNew method has been used against the Recordset held by the Data control, or the user has attempted to move past the EOF position of the Recordset when the EOFAction property has been set to AddNew.
  
  
• vbDataActionUpdate. The Update method has been used against the Recordset held by the Data control.
  
  
• vbDataActionDelete. The Delete method has been used against the Recordset held by the Data control.
  
  
• vbDataActionFind. One of the find methods has been used against the Recordset held by the Data control.
  
  
• vbDataActionBookmark. The Bookmark property of the Recordset held by the Data control has been changed.
  
  
• vbDataActionClose. The Close method has been used against the Recordset held by the Data control.
  
  
• vbDataActionUnload. The form containing the Data control has had the Unload method applied to it.
  
  
• VbDataActionCancel. Undoes changes to bound controls caused by the foregoing actions.
  
  

To practice handling a Validate event, make the following changes to your project:

1. In Form1, change the DataField property of the txtIsbn control back to isbn.
   
   
2. Add the following code to the Data1_Validate event:
   
   If Save = False Then
   
   Exit Sub
   
   End If
   
   
   If MsgBox(" Save Changed Record ", vbYesNo, "", "", 0) = vbNo Then
   
   Action = 0
   Exit Sub
   
   End If
   
   
3. Run your project.
   
   
4. Change the contents of a number of fields in the current record.
   
   
5. Use the Data control to move to another record or try to unload the form. You will be prompted in order to determine whether you want to save the changes (see Figure 27.5).
   
   
6. Select Yes to save your changes. Select No to ignore the changes.
   
   

Figure 27.5. Updating a record often causes a dialog box to appear.

Additional Methods Provided with the Data Control

The Data control supports all of the standard methods, such as Z-Order, and Drag methods that are associated with Visual Basic controls. In addition to the standard methods, the Data control supports methods that relate to data manipulation. The following sections discuss these data manipulation methods.

Refresh

The Refresh method is used in a variety of ways with the Data control. This chapter used the Refresh method in order to effect changes to the underlying Recordset after a property in the Data control had been changed. In addition to the examples previously illustrated, you must invoke the Refresh method in order to place a newly added record into the correct order in a currently open Recordset. You need to invoke the Refresh method because the default position for the newly added record is at the end of the Recordset. In addition, the Refresh method is needed in order to allow the newly added record to be visible to other users of a similarly created Recordset. The same would also be true of editing a field that was part of the order by clause in an SQL statement or changing a field that composed the index of a table. In a multi-user environment, it is important to ensure that an update is always executed after adding, changing, or deleting records so that all users have access to an up-to-date database.

Note

If you are using transaction controls, you must ensure that a Refresh method is not used inside a BeginTrans/CommitTrans block since this action will cause a runtime error.

To practice using the Refresh method, make the following changes to your project:

1. Add the following command button to your form:
   
   Name= cmdadd
   Caption= &Add
   Top= 4080
   Width = 795
   Height= 295
   left = 4440
   
   
2. Add the following command button to your form:
   
   Name= cmdrefresh
   Caption= &Refresh
   Top= 4080
   Width = 855
   Height= 295
   left = 3420
   
   
3. Add the following code to the Cmdadd_Click event:
   
   data1.Recordset.addnew
   
   
4. Add the following code to the Cmdrefresh_Click event:
   
   data1.refresh
   
   
5. Run your project.
   
   
6. Press the Add button; a blank screen appears.
   
   
7. Enter the data for the new record into the available text boxes.
   
   
8. Select the move to the next record button to save your record and move to the next record.
   
   
9. Move to the previous record, and note that your new record is not there.
   
   
10. Move to the last record in the Recordset, and note that this is the record that you entered.
    
    
11. Select the Refresh button. The Refresh method causes the Data control to rebuild its underlying Recordset and position the current record to the first record in the set. Your newly added record now appears in the correct position of the Recordset.
    
    

UpdateControls

The UpdateControls method will reset the bound controls to the values they had prior to any editing. This method provides the same effect as re-reading the current record. An example of using the UpdateControls method would be when the user decides that he does not want to save any changes that have been made.

To practice using the UpdateControls method, make the following changes to your project:

1. Add the following to the data1_Validate event:
   
   data1_Validate (action as integer , save as integer)
   
   
   if save= false then
   
   exit sub
   
   endif
   
   if msgbox (" Save Changed Record ",vbyesno,"","",0)= vbNo then
   
   data1.updatecontrols
   exit sub
   
   endif
   
   
2. Run your project.
   
   
3. Change a number of fields in the current record.
   
   
4. Use the Data control to move to another record or try to unload the form. You are prompted in order to determine whether you want to save the changes.
   
   
5. Select Yes to save your changes. Select No to ignore the changes.
   
   

UpdateRecord

The UpdateRecord method causes the contents of the bound controls to be updated to the database. This method allows you to save any changes and keep the record current. In contrast, previous examples used the Data control to save the record changes only when the record pointer was changed.

To practice using the UpdateRecord method, make the following changes to your project:

1. Add the following command button to your form:
   
   Name= cmdupdate
   Caption= &Update
   Top= 4080
   Width = 855
   Height= 295
   left = 2460
   
   
2. Add the following code to the cmdupdate_Click event:
   
   data1.updaterecord
   
   
3. Run your project.
   
   
4. Make a change to the currently displayed record.
   
   
5. Select the Update button. The changes that you have made to the record are saved, and the changed record remains the current record (see Figure 27.6).
   
   

Figure 27.6. The final appearance of the bound control form.

Using the Data Control with Non-Access Databases

The Data control enables you to access databases other than MS Access databases. To access an external(non-MS Access) database, you must make an entry in the Connect property that matches the type of database you are trying to access. Once you have connected to the external database, it will behave like a native Access database.

Perform the following steps in order to practice connecting to an external database:

1. Using either MS Access or the Visdata sample application that comes with Visual Basic, export the TITLES table contained in the BIBLIO.MDB MS Access database to a DBASE III format file named TITLE.DBF. Save the exported file to same directory as BIBLIO.MDB.
   
   
2. Make a copy of the Form1 form and name it Form3.
   
   
3. In the Project Options dialog box, change the start-up form to Form3.
   
   
4. Alter the form caption for Form3 to Bound DBASE.
   
   
5. Make the following changes to the Data control's properties:
   
   Connect = DBASE III;
   DataBase= The Directory where the TITLE.DBF resides.
   RecordSource= title
   Ensure that the Recordsettype is set to Dynaset.
   
   
6. Remove the existing code from the Form_Load event and add the following code:
   
   sub Form_Load
   Dim x As Integer
   
   Data1.Refresh
   
   For x = 0 To Data1.Recordset.Fields.Count - 1
   comfield.AddItem Data1.Recordset.Fields(x).Name
   Next x
   
   end sub
   
   
7. Run the project.
   
   

Note that the application behaves in the same manner as when an MS Access format database was used.

Note

When you are using non-MS Access databases, you must use the SQL syntax of the attached database, not that of MS Access.

Using the Data Control to Delete Records

So far in this chapter, you have looked at all of the properties, methods, and events that are associated with the Data control. However, there has not been an example presented on how records are removed from a Recordset. The reason for this is that there is not a delete type of action provided with the Data control. The following example will build upon the final bound control example and add a method in order to delete a record from the underlying Recordset.

Make the following changes to your project:

1. Set the start-up form in your project to Form1.
   
   
2. Add the following command button to your form:
   
   Name= cmdDelete
   Caption= &Delete
   Top= 4080
   Width =855
   Height= 295
   left = 1440
   
   
3. Add the following code to the cmdDelete_Click event
   
   data1.Recordset.delete
   cmdrefresh_Click
   
   
4. Run your project.
   
   

When you select the Delete button, the currently displayed record is deleted and the Recordset is updated. You could perform the same steps to provide the same functionality to the Bound DBASE form.

Summary

This chapter covered the various properties, methods, and events of the Data control. The examples provided have used simple examples in order to illustrate the capabilities of the control. The final version of the form you created allowed you to sort, display, edit, delete, and add records to the TITLES table of the BIBLIO database as well as that of the external TITLE.DBF file.

In addition to the Data control, you had a very brief look at the Recordset property of the Data control. The Recordset property is very powerful as it allows you to fully exploit the properties and methods of the Jet database engine.

In the following chapter, you will see additional properties and methods that can be applied to Data Access Objects. You can apply the majority of these properties and methods to the Data control through the use of the Recordset property.